
package com.scalar.chiptrack.jobs.dao;

import java.util.Date;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Map;
import java.util.HashMap;

import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.io.IOException;

import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ClassUtils;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.jobs.Job;
import com.scalar.chiptrack.jobs.ReportConfiguration;
import com.scalar.chiptrack.operations.reports.DBColumn;
import com.scalar.chiptrack.operations.reports.DataDumpReportsHelper;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.ScalarException;

import org.apache.poi.ss.usermodel.Workbook;

public final class JobDAO {


    private JobDAO() {
    }

    // added
    public static void abortAllRunningJobs() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement ("update job_status set end_time = Null, Time_Taken = NULL, status ='"+ Job.STATUS_ABORTED +"'  where Status = '"+Job.STATUS_RUNNING +"'");
            pstmt.executeUpdate();

            success = true;
            System.out.println("Abort all running jobs at Job Runner getting instantce");
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not able to Abort all running jobs at Job Runner getting instantce ", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    } // end of the method  added


    public static void removeJobById (String[] jobStatusIdArray) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        if (jobStatusIdArray == null) {
            return;
        }

        try {

            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.deleteJobById"));
            for (int i=0; i<jobStatusIdArray.length; i++) {
                pstmt.setInt (1, Integer.parseInt (jobStatusIdArray[i]));
                pstmt.addBatch();
            }

            pstmt.executeBatch();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not delete row(s) from Job_Status table", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

// **----------- Modified by Sharath Reddy on 30th May 2003. -----------------------------------------------------------
//   This method is used to set the Removed flag to 0 in the JOBS table.------------------------------------------------
//   Return Value - Method Returns  void
//   Parameter - Accepts an int value(jobId)
// **----------- Modified by Sharath Reddy on 30th May 2003. -----------------------------------------------------------

    public static void removeJobByIdInJobs (String jobId, String removed) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
           conn = DataSource.getConnection();
           pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.deleteJobByIdInJobs"));

           pstmt.setInt (1, Integer.parseInt(removed));
           pstmt.setInt (2, Integer.parseInt(jobId));

           pstmt.executeUpdate();
           success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not delete row(s) from Jobs table", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

// **----------- Modified by Sharath Reddy on 29th May 2003. -----------------------------------------------------------
//   This method is used to set the Removed flag to 0 in the JOBS table.------------------------------------------------
// **----------- Modified by Sharath Reddy on 29th May 2003. -----------------------------------------------------------


    public static void updateJobState (int jobStatusId, String status, Date endTime, long timeTaken) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {

            conn = DataSource.getConnection();

            // INSERT JOB
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.updateJobStatusAndTimeTaken"));
            pstmt.setString (1, status);
            pstmt.setLong (2, timeTaken);
            pstmt.setTimestamp (3, new java.sql.Timestamp (endTime.getTime()));
            pstmt.setInt (4, jobStatusId);

            pstmt.executeUpdate();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Job state in database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

    }


    public static int insertJobStatusByJobHandler (int jobId, String jobHandler, String status, Date startTime) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int statusId = -1;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            // Step 1
            // Cleanup the database
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.cleanupJobStatusInfo"));
            pstmt.setInt (1, ConfigManager.getDaysToKeepAdaptorRunInfo());
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            // INSERT ROW INTO JOB_STATUS
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.insertJobStatusAndStartTime"));
            pstmt.setInt (1, jobId);
            pstmt.setString (2, status);
            pstmt.setTimestamp (3, new java.sql.Timestamp (startTime.getTime()));
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            // GET JOB_STATUS_ID
            pstmt = conn.prepareStatement ("SELECT @@IDENTITY as status_id");
            rset = pstmt.executeQuery();
            if (rset.next() != false) {
                statusId =  rset.getInt("status_id");
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            // UPDATE START_TIME IN JOBS TABLE
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.updateJobStartTimeByJobHandler"));
            pstmt.setTimestamp (1, new java.sql.Timestamp (startTime.getTime()));
            pstmt.setString (2, jobHandler);
            pstmt.execute();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Job state in database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return statusId;
    }

    public static int insertJobStatus (int jobId, String status, Date startTime) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int statusId = -1;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            // INSERT ROW INTO JOB_STATUS
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.insertJobStatusAndStartTime"));
            pstmt.setInt (1, jobId);
            pstmt.setString (2, status);
            pstmt.setTimestamp (3, new java.sql.Timestamp (startTime.getTime()));
            pstmt.execute();
            pstmt.close();
            pstmt = null;

            // GET JOB_STATUS_ID
            pstmt = conn.prepareStatement ("SELECT @@IDENTITY as status_id");
            rset = pstmt.executeQuery();
            if (rset.next() != false) {
                statusId =  rset.getInt("status_id");
            }
            rset.close();
            pstmt.close();
            pstmt = null;

            // UPDATE START_TIME IN JOBS TABLE
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.updateJobStartTime"));
            pstmt.setTimestamp (1, new java.sql.Timestamp (startTime.getTime()));
            pstmt.setInt (2, jobId);
            pstmt.execute();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Job state in database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return statusId;

    }


    public int getJobIdByJobStatusId (int jobStatusId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        int jobId = -1;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.getJobIdByJobStatusId"));
            pstmt.setInt (1, jobStatusId);
            rset = pstmt.executeQuery();

            if (rset.next()) {
                jobId = rset.getInt ("Job_ID");
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not get Job ID for Job Status ID: " + jobStatusId, sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return jobId;
    }


    public static Job loadJobById (int jobId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        Job job = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.loadJobById"));
            pstmt.setInt (1, jobId);
            rset = pstmt.executeQuery();

            long freqInHours = 0;
            long freqInMinutes = 0;

            if (rset.next()) {

            	int frequency = rset.getInt("Frequency");

				if(frequency<60){
					freqInHours = 0;
            		freqInMinutes = frequency;

				}else{

					freqInHours = frequency/60;
					freqInMinutes = frequency - (freqInHours * 60);
				}

                job = new Job (rset.getInt ("Job_ID"),
                                   rset.getString ("Job_Name"),
                                   rset.getString ("Job_Handler"),
                                   rset.getTimestamp("Start_Time"),
                                   freqInHours,
                                   freqInMinutes);
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Jobs from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return job;
    }

    public static Job loadJobScheduleInfoById (int jobId) throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        Job job = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.loadJobScheduleInfoById"));
            pstmt.setInt (1, jobId);
            rset = pstmt.executeQuery();
            long freqInHours = 0;
            long freqInMinutes = 0;

            if (rset.next()) {

				int frequency = rset.getInt("Frequency");

				if(frequency<60){
					freqInHours = 0;
            		freqInMinutes = frequency;
				}else{
					freqInHours = frequency/60;
					freqInMinutes = frequency - (freqInHours * 60);
				}

                job = new Job (rset.getInt ("Job_ID"), rset.getString ("Job_Name"), rset.getString ("Job_Handler"),
                               rset.getTimestamp("Start_Time"), freqInHours, freqInMinutes);  //converting min to hrs

                String occurs       = rset.getString("occurs");
                String scheduleDays = rset.getString("Schedule_Days");
                String every        = rset.getString("every");
                String emails       = rset.getString("emails");
                job.setEmails( emails != null ? emails : "" );

                if ( occurs == null || occurs.length() == 0)  {
                        occurs = "Daily";
                }  else {
                        occurs = occurs.trim();
                }

                if ( scheduleDays == null )  {
                    scheduleDays = "";
                } else  {
                     scheduleDays = scheduleDays.trim ();
                }

                if ( every == null ) {
                    every = "";
                }

                if ( occurs.equalsIgnoreCase("Daily") )
                {
                    job.setEveryDaily( every );
                }
                else if ( occurs.equalsIgnoreCase("Weekly") )
                {
                    java.util.StringTokenizer stTokenizer = new java.util.StringTokenizer (scheduleDays, ";");
                    String selectedWeekDays [] = new String [stTokenizer.countTokens()];
                    int idx =0;
                    while (stTokenizer.hasMoreTokens())
                        selectedWeekDays[idx++] = stTokenizer.nextToken();

                    job.setSelectedWeekDays( selectedWeekDays );

                    job.setEveryWeekly( every );
                }
                else if ( occurs.equalsIgnoreCase("Monthly") )
                {
                    job.setEveryDay( scheduleDays );
                    job.setEveryMonthly( every );
                }

                job.setOccurs( occurs );
                job.setStartDate( com.scalar.chiptrack.utils.StringUtils.formatDateToString( rset.getDate("Start_Date"),"MM/dd/yyyy") );
                job.setEndDate( com.scalar.chiptrack.utils.StringUtils.formatDateToString( rset.getDate("End_Date"),"MM/dd/yyyy") );

                java.util.Calendar cal = java.util.Calendar.getInstance ();

                if  ( rset.getTimestamp("Schedule_Time") != null)
                {
                    cal.setTimeInMillis( rset.getTimestamp("Schedule_Time").getTime() );
                }

                //when we set the schedular time to 12:01 12:02 ...12:59 AM, In db it saved as 00:01 ,00:02..00:59
                //so when hour is zero, we setting it to 12
                if ( cal.get (java.util.Calendar.HOUR) == 0 )
                {
                    job.setScheduleTime("12");
                }
                else
                {
                    job.setScheduleTime( cal.get (java.util.Calendar.HOUR) + "");
                }

                if ( (cal.get (java.util.Calendar.MINUTE) >= 0 ) && (cal.get (java.util.Calendar.MINUTE) <= 9))
                {
                    job.setScheduleMinute("0" + cal.get (java.util.Calendar.MINUTE) );
                }
                else
                {
                    job.setScheduleMinute(cal.get (java.util.Calendar.MINUTE) + "");
                }
                job.setAmPm(cal.get (java.util.Calendar.AM_PM ) );

                job.setFtpUrl( rset.getString("Ftp_Url"));
                job.setDirectory( rset.getString("Directory"));
                job.setUserName( rset.getString("User_Name"));
                job.setPassword( rset.getString("Password"));
                job.setConnectionMode( rset.getString("Ftp_Conn_Mode"));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Job from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return job;
    }

    public static ArrayList loadAllJobStatusInfo() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList jobList = new ArrayList();
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.loadAllJobStatusInfo"));
            rset = pstmt.executeQuery();

            int id = -1;
            int statusId = -1;
            String name = null;
            Date startTime = null;
            Date endTime = null;
            long timeTaken = -1;
            String status = null;

            while (rset.next()) {
                id = rset.getInt ("Job_ID");
                statusId = rset.getInt ("Job_Status_ID");
                name = rset.getString ("Job_Name");
                Timestamp timestamp = rset.getTimestamp("Start_Time");

                startTime = new Date (timestamp.getTime() + (timestamp.getNanos()/1000000));

                timestamp = rset.getTimestamp("End_Time");
                if (timestamp != null) {    // End_Time is null for running jobs
                    endTime = new Date (timestamp.getTime() + (timestamp.getNanos()/1000000));
                }

                timeTaken = rset.getLong ("Time_Taken");
                status = rset.getString ("Status");

                jobList.add (new Job (id, statusId, name, startTime, endTime, timeTaken, status));
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Job Status information from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return jobList;
    }

    //modified by phani 09/07/2004
    public synchronized static ArrayList loadAllJobs() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList jobList = new ArrayList();
        ResultSet rset = null;

        String occurs = null;
        String scheduleDays = null;
        String every = null;
        String emails = null;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.loadAllJobs"));
            rset = pstmt.executeQuery();
            int id = -1;

            String name = null;
            String handler = null;
            Date startTime = null;
            long freq = -1;
            Job job;
            while (rset.next()) {

                id = rset.getInt ("Job_ID");
                name = rset.getString ("Job_Name");
                handler = rset.getString ("Job_Handler");

                Timestamp timestamp = rset.getTimestamp("Start_Time");

                if (timestamp != null) {
                    startTime = new Date (timestamp.getTime() + (timestamp.getNanos()/1000000));
                }

                freq = rset.getLong ("Frequency");
                job = new Job (id, name, handler, startTime, freq);

                occurs       = rset.getString("occurs");
                scheduleDays = rset.getString("Schedule_Days");
                every        = rset.getString("every");

                if ( occurs == null || occurs.length() == 0)  {
                        occurs = "Daily";
                }  else {
                        occurs = occurs.trim();
                }

                if ( scheduleDays == null )  {
                    scheduleDays = "";
                } else  {
                     scheduleDays = scheduleDays.trim ();
                }

                if ( every == null ) {
                    every = "";
                }

                if ( occurs.equalsIgnoreCase("Daily") )
                {
                    job.setEveryDaily( every );
                }
                else if ( occurs.equalsIgnoreCase("Weekly") )
                {
                    java.util.StringTokenizer stTokenizer = new java.util.StringTokenizer (scheduleDays, ";");
                    String selectedWeekDays [] = new String [stTokenizer.countTokens()];
                    int idx =0;
                    while (stTokenizer.hasMoreTokens())
                        selectedWeekDays[idx++] = stTokenizer.nextToken();

                    job.setSelectedWeekDays( selectedWeekDays );

                    job.setEveryWeekly( every );
                }
                else if ( occurs.equalsIgnoreCase("Monthly") )
                {
                    job.setEveryDay( scheduleDays );
                    job.setEveryMonthly( every );
                }

                job.setOccurs( occurs );
                job.setStartDate( com.scalar.chiptrack.utils.StringUtils.formatDateToString( rset.getDate("Start_Date"),"MM/dd/yyyy") );
                job.setEndDate( com.scalar.chiptrack.utils.StringUtils.formatDateToString( rset.getDate("End_Date"),"MM/dd/yyyy") );

                java.util.Calendar cal = java.util.Calendar.getInstance ();

                if  ( rset.getTimestamp("Schedule_Time") != null)
                    cal.setTimeInMillis( rset.getTimestamp("Schedule_Time").getTime() );

                job.setScheduleTime( cal.get (java.util.Calendar.HOUR) + "");
                job.setScheduleMinute( cal.get(java.util.Calendar.MINUTE) +"");
                job.setAmPm(cal.get (java.util.Calendar.AM_PM ) );

                job.setEmails( rset.getString("emails") != null ? rset.getString("emails") : "");
                job.setFtpUrl( rset.getString("Ftp_Url"));
                job.setDirectory( rset.getString("Directory"));
                job.setUserName( rset.getString("User_Name"));
                job.setPassword( rset.getString("Password"));
                job.setConnectionMode( rset.getString("Ftp_Conn_Mode"));
                jobList.add (job);
                startTime = null;
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Jobs from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }

        return jobList;
    }


    private static ArrayList buildJobListFromResultSet (ResultSet rset) throws SQLException {

        ArrayList jobList = new ArrayList();

        while (rset.next()) {

            jobList.add (new Job (rset.getInt ("Job_ID"),
                                   rset.getString ("Job_Name"),
                                   rset.getString ("Job_Handler"),
                                   rset.getTimestamp("Start_Time"),
                                   rset.getLong ("Frequency")));
        }
        return jobList;
    }

    public static void insertJob (Job job) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try {
            conn = DataSource.getConnection();

            // INSERT JOB
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.insertJob"));
            pstmt.setString (1, job.getName());
            pstmt.setString (2, job.getHandler());
            pstmt.setDate (3, new java.sql.Date (job.getStartTime().getTime()));
            pstmt.setLong (4, job.getTimeTaken());
            pstmt.setLong (5, job.getFrequencyInHours());
            pstmt.setString (6, job.getStatus());
            pstmt.execute();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not store Job into database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

	// Method added by Raj Vallamsetla

	public static int insertJob (DBTransaction dbTransaction,Job job) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rset = null;
        boolean success = false;
		int jobId		= -1;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

            Date dateStartTime = job.getStartTime();
			java.sql.Date sqlStartTime = null;
			if(dateStartTime !=null)
			sqlStartTime = new java.sql.Date (job.getStartTime().getTime());


            // INSERT JOB
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.insertJob"));
            pstmt.setString (1, job.getName());
            pstmt.setString (2, job.getHandler());
            pstmt.setDate (3, sqlStartTime);
            //pstmt.setLong (4, job.getFrequency());
            pstmt.setInt (4, (job.isSystem() ? 0 : 1));
            pstmt.executeUpdate();
            pstmt.close();
            pstmt = null;

            pstmt = conn.prepareStatement ("SELECT @@IDENTITY as job_id");
            rset = pstmt.executeQuery();


            if (rset.next() != false) {
                jobId =  rset.getInt ("job_id");
            }

            rset.close();
            rset = null;

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not store Job into database", sqle);
        }
        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
		  return jobId;

    }

	public static void updateJob (DBTransaction dbTransaction,Job job) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rset = null;
        boolean success = false;
		int jobId		= -1;

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }


            // INSERT JOB
            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.updateJob"));
			//pstmt.setLong(1,job.getFrequency());
            pstmt.setString (1, job.getName());
            pstmt.setString (2, job.getHandler());
			pstmt.setInt(3,job.getId());
            pstmt.executeUpdate();

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Job into database", sqle);
        }
        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }


    }


	public static void deleteJob (DBTransaction dbTransaction,ArrayList jobList) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
		ResultSet rset = null;
        boolean success = false;
		String jobId = "";
		String inClause = "";

        try {
            if (dbTransaction == null) {
                conn = DataSource.getConnection();
            }
            else {
                conn = dbTransaction.getConnection();
            }

			for(int i=0;i<jobList.size();i++){
            jobId =(String)jobList.get(i);
            if(i!=jobList.size()-1){
			jobId = jobId+",";
			}
			inClause +=jobId;
			}
			System.out.println("inClause "+inClause);
			if(inClause.equals(""))
				return;

			else{
            // Delete JOB
			String sql ="Delete from jobs where job_Id in( "+inClause+" )";
            pstmt = conn.prepareStatement (sql);
			pstmt.executeUpdate();
			}

        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not update Job into database", sqle);
        }
        finally {

            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }


    }



    public static ArrayList loadAllSystemJobs() throws ScalarException {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList jobList = new ArrayList();
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.loadAllSystemJobs"));
            //pstmt = conn.prepareStatement ("SELECT * FROM Jobs WHERE System = 0");
            rset = pstmt.executeQuery();

            int id = -1;
// **----------- Modified by Sharath Reddy on 29th May 2003.
            String jobName = null;
// **----------- Modified by Sharath Reddy on 29th May 2003.
            String handler = null;
 // **----------- Modified by Sharath Reddy on 30th May 2003.
             boolean isDisabled = false;
// **----------- Modified by Sharath Reddy on 30th May 2003.
            Date startTime = null;
            long freqInHours = -1;
            long freqInMinutes = -1;

            while (rset.next()) {

                id = rset.getInt ("Job_ID");
                jobName = rset.getString ("Job_Name");
                handler = rset.getString ("Job_Handler");

                Timestamp timestamp = rset.getTimestamp("Start_Time");

                if (timestamp != null) {
                    startTime = new Date (timestamp.getTime() + (timestamp.getNanos()/1000000));
                }

                //freqInHours = rset.getLong ("Frequency")/60;   //convert into hours

                int frequency = rset.getInt("Frequency");

				if(frequency<60){
					freqInHours = 0;
            		freqInMinutes = frequency;

				}else{

					freqInHours = frequency/60;
					freqInMinutes = frequency - (freqInHours * 60);
				}

            	//System.out.println("Job ID = "+id+"Freq Hrs = "+freqInHours+" min="+freqInMinutes);

                isDisabled = ((rset.getInt("Removed") == 0) ? true : false);

                jobList.add (new Job (id, jobName, handler, startTime, freqInHours, freqInMinutes, isDisabled));
                startTime = null;
            }
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not load Jobs from database", sqle);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        //System.out.println("Jobs Size is = " + jobList.size());
        //Here a small problem, this will work only if there is no jobs in the db, if you want to add a new DB need to modify this code
        //for time being i just leaving like this sorry ;)
        if(jobList.size() == 0)
        {
               jobList.add (new Job (-1,"Adapter Job","com.scalar.chiptrack.adaptor.AdaptorJob",new java.util.Date(),0));
               jobList.add (new Job (-1,"Adapter Logs Collector Job","com.scalar.chiptrack.jobs.systemjobs.CollectAdaptorLogsJob",new java.util.Date(),0));
        }
        return jobList;


    }


    public static ArrayList loadSystemJobs() throws ScalarException 
	{
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        ArrayList jobList 		= new ArrayList();
        boolean success         = false;

        try 
		{
        	conn = DataSource.getConnection();

            pstmt = conn.prepareStatement ( "INSERT INTO JOBS ( JOB_NAME, JOB_HANDLER, REMOVED, [SYSTEM] ) SELECT DISTINCT REPORT_NAME, '"+ Constants.REPORTS_JOB_HANDLER_CLASS+"', 0, 0 FROM REPORT_CONFIGURATION_TABLE WHERE REPORT_NAME NOT IN ( SELECT JOB_NAME FROM JOBS )" );
            pstmt.executeUpdate();

            pstmt.close(); pstmt = null;

            pstmt 	= conn.prepareStatement ( SqlQueryMappings.get ( "jobs.loadSystemJobs" ) );
            rset 	= pstmt.executeQuery();
            int id 	= -1;
			String jobName = null;
			String handler = null;
			Date startTime = null;
			long freqInHours 	= -1;
			long freqInMinutes 	= -1;
			boolean isDisabled 	= false;

			while( rset.next() ) 
			{
				id 		= rset.getInt( "Job_ID" );
				jobName = rset.getString( "Job_Name" );
				handler = rset.getString( "Job_Handler" );
                Timestamp timestamp = rset.getTimestamp( "Start_Time" );

                if( timestamp != null ) 
                {
                	startTime = new Date (timestamp.getTime() + (timestamp.getNanos()/1000000));
                }

                int frequency = rset.getInt("Frequency");

				if( frequency < 60 )
				{
					freqInHours = 0;
	            	freqInMinutes = frequency;
				}
				else
				{
					freqInHours = frequency/60;
					freqInMinutes = frequency - (freqInHours * 60);
				}

				isDisabled = ((rset.getInt("Removed") == 0) ? true : false);
                java.util.Calendar cal = java.util.Calendar.getInstance ();

                if  ( rset.getTimestamp("Schedule_Time") != null)
                    cal.setTimeInMillis( rset.getTimestamp("Schedule_Time").getTime() );

                String scheduleTime =( cal.get (java.util.Calendar.HOUR) + "");
                String scheduleMinute =( cal.get(java.util.Calendar.MINUTE) +"");
                int amPm = (cal.get (java.util.Calendar.AM_PM ) );

                jobList.add (new Job (id, jobName, handler, startTime, freqInHours, freqInMinutes, isDisabled, scheduleTime, scheduleMinute, amPm));
                startTime = null;
			}
            conn.commit();
            success = true;
		}
        catch( Exception sqle ) 
		{
        	sqle.printStackTrace();
            throw new ScalarException ("Could not load Jobs from database", sqle);
		}
        finally 
		{
            if ( conn != null && !success )
            {
                try{ conn.rollback(); } catch (SQLException ignore) {}
            }
        	if ( rset != null ) {
        		try { rset.close(); } catch (SQLException ignore){}
        	}
            if ( pstmt != null ) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null ) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
        
        // This is commented by ravikanth as these files no longer are user full
        /*if( jobList.size() == 0 )
        {
               jobList.add( new Job( -1, "Adapter Job", "com.scalar.chiptrack.adaptor.AdaptorJob", new java.util.Date(), 0 ) );
               jobList.add( new Job( -1, "Adapter Logs Collector Job", "com.scalar.chiptrack.jobs.systemjobs.CollectAdaptorLogsJob", new java.util.Date(), 0 ) );
        }*/
        
        return jobList;
	}

    public static void udpateJobScheduleInfo (Job job) throws ScalarException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try 
		{
            conn = DataSource.getConnection();

            String scheduleDays = "";
            String every = "";
            int scheduleTimeInHours = 0;
            int scheduleTimeInMinutes = 0;

            if ( job.getOccurs().equalsIgnoreCase("Daily") )
            {
                every = job.getEveryDaily ();
            }
            else if ( job.getOccurs().equalsIgnoreCase("Weekly") )
            {
                every = job.getEveryWeekly();

                int no_of_week_selected = job.getSelectedWeekDays().length;
                String weeks[] = new String[no_of_week_selected] ;
                weeks = job.getSelectedWeekDays();
                StringBuffer sb = new StringBuffer();

                for (int i =0; i< no_of_week_selected; i++ )
                {
                    sb.append( weeks[i] + ";");
                }
                if ( sb.length() > 0 && sb.charAt( sb.length() -1 ) == ';' )
                {
                    sb.deleteCharAt( sb.length() -1 );
                }

                scheduleDays = sb.toString();
            }
            else if ( job.getOccurs().equalsIgnoreCase("Monthly") )
            {

                every = job.getEveryMonthly ();
                scheduleDays = job.getEveryDay();
            }

            try
            {
                scheduleTimeInHours = Integer.parseInt(job.getScheduleTime());
                scheduleTimeInMinutes = Integer.parseInt(job.getScheduleMinute() );
            }
            catch (NumberFormatException nfe)  { }

            Calendar cal = Calendar.getInstance();

            // If 12:00 ,12:01,12:02 ....   12:59 AM (or) PM means(according to system) 00:00,00:01,00:02 ...... 00:59AM (or) PM
            if( scheduleTimeInHours == 12 )
            {
                cal.set( Calendar.HOUR, 0 );
            }
            else
            {
                cal.set( Calendar.HOUR, scheduleTimeInHours);
            }

            cal.set( Calendar.AM_PM, job.getAmPm());
            //cal.set( Calendar.MINUTE, 0);
            cal.set( Calendar.MINUTE, scheduleTimeInMinutes);
            cal.set( Calendar.SECOND, 0);
            cal.set( Calendar.MILLISECOND, 0);

            pstmt = conn.prepareStatement (SqlQueryMappings.get ("jobs.updateJobScheduleInfo"));
            pstmt.setString (1, job.getStartDate());
            pstmt.setString (2, ( job.getEndDate()!= null && job.getEndDate().length() > 0 )? job.getEndDate() : null);
            pstmt.setTimestamp(3, new java.sql.Timestamp ( cal.getTime().getTime() ));
            pstmt.setString (4, scheduleDays);
            pstmt.setLong (5, job.getFrequencyInHours() * 60+job.getFrequencyInMinutes());
            pstmt.setString(6, every);
            pstmt.setString(7, job.getOccurs());
            pstmt.setString(8, job.getEmails());
            pstmt.setString(9, job.getFtpUrl());
            pstmt.setString(10, job.getDirectory());
            pstmt.setString(11, job.getUserName());
            pstmt.setString(12, job.getPassword());
            pstmt.setString(13, job.getConnectionMode());
            pstmt.setInt(14, job.getId());

            pstmt.execute();

            success = true;
        }
        catch (Exception sqle) {
            sqle.printStackTrace();
            throw new ScalarException ("Could not store Job Schedule Info into database", sqle);
        }
        finally {
            try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }



    public static void updateSchedulerStatus( Boolean status ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "update Job_Scheduler_Status set Scheduler_Status = " + ( ( status == Boolean.FALSE ) ? 1 : 0 ) + "" );
            pstmt.executeUpdate();
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not able to Update the Scheduler Status ", sqle );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }



    public static boolean getSchedulerStatus()
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        boolean status = false;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "select Scheduler_Status from Job_Scheduler_Status" );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                status = ( ( rset.getString( "Scheduler_Status" ) != null && rset.getInt( "Scheduler_Status" ) == 0 ) ? true : false );
            }
            else
            {
                if ( rset != null )
                {
                    rset.close();
                }

                if ( pstmt != null )
                {
                    pstmt.close();
                }

                pstmt = conn.prepareStatement( "insert into Job_Scheduler_Status ( Scheduler_Status ) values ( 1 ) " );
                pstmt.execute();
            }
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            System.out.println( "Could not able to Retrive the Schedule Status" + sqle );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return status;
    }

    /**
     * updates On Demand Adaptor Running Status. 0 menas Running Status.
     * @param status
     * @throws ScalarException
     */

    public static void updateOnDemandAdaptorStatus( Boolean status ) throws ScalarException
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "UPDATE Job_Scheduler_Status SET ON_DEMAND_ADAPTOR_STATUS = " + ( ( status == Boolean.FALSE ) ? 1 : 0 ) + ", Timestamp = CURRENT_TIMESTAMP" );
            pstmt.executeUpdate();
            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not able to Update the On Demand Adaptor Status ", sqle );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }

    /**
     * Returns the On Demand Adaptor Stutus. 0 means Runnning Status.
     * @return
     */

    public static boolean getOnDemandAdaptorStatus()
    {

        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        boolean status = false;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT ON_DEMAND_ADAPTOR_STATUS FROM JOB_SCHEDULER_STATUS" );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                status = ( ( rset.getString( "ON_DEMAND_ADAPTOR_STATUS" ) != null && rset.getInt( "ON_DEMAND_ADAPTOR_STATUS" ) == 0 ) ? true : false );
            }

            success = true;
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            System.out.println( "Could not able to Retrive the Schedule Status" + sqle );
        }
        finally
        {
            try
            {
                if ( conn != null )
                {
                    if ( success == true )
                    {
                        conn.commit();
                    }
                    else
                    {
                        conn.rollback();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }

            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }

        return status;
    }

    /**
     * to update the on demand adaptor status if the given timeout(minutes) is elapsed
     * @param dbTransaction
     * @param status
     * status to be updated for on_demand_adaptor_status
     * @param timeOut
     * timeOut in minutes
     * @throws ScalarException
     */
    public static void updateOnDemandAdaptorStatusOnTimeOut ( DBTransaction dbTransaction, boolean status, int timeOut ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success         = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "UPDATE JOB_SCHEDULER_STATUS SET ON_DEMAND_ADAPTOR_STATUS = ?, Timestamp = CURRENT_TIMESTAMP WHERE DATEDIFF ( mi, Timestamp, CURRENT_TIMESTAMP ) >= ?" );

            pstmt.setInt ( 1, ( status ? 0 : 1 ) );
            pstmt.setInt ( 2, timeOut );

            pstmt.executeUpdate();
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "could not update the on demand adaptor status on timeout" );
        }
        finally
        {
            if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                    catch( Exception e )
                    {
                        System.out.println ( "-------COULD NOT COMMIT/ROLLBACK------" );
                        e.printStackTrace();
                    }
                }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
    }

    /*
     * @author ravi
     * Fetch Job ID by Job Name
     * 
     */
    public static int fetchJobIdByName( String jobName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        int jobId = -1;

        try
        {
            conn    = DataSource.getConnection();
            pstmt = conn.prepareStatement ( "select Job_Id from JOBS where Job_Name = ?" );
            pstmt.setString ( 1, jobName );

            rset = pstmt.executeQuery();
            
            if ( rset.next() )
            {
                jobId = rset.getInt( "Job_Id" );
            }
        }
        catch( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "could not fetch the Job Id" );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) { }
            }
        }
        return jobId;
    }
    public static ArrayList loadAllJobSetupInfoExcel ( DBTransaction dbTransaction ) throws ScalarException
     {
             Connection conn         = null;
             PreparedStatement pstmt = null;
             ResultSet rset          = null;
             boolean success         = false;
             int colCount            = 0;
             ArrayList jobList = new ArrayList();
             ArrayList colList       = new ArrayList();
             DBColumn dbColumn       = null;

             try
             {
                 if ( dbTransaction != null )
                 {
                     conn    = dbTransaction.getConnection();
                 }
                 else
                 {
                     conn    = DataSource.getConnection();
                 }

                 pstmt   = conn.prepareStatement ( "{ CALL SP_JOB_SETUP_PROC()}" );

                 rset    = pstmt.executeQuery();

                 colCount = rset.getMetaData().getColumnCount();

                 for ( int j = 1; j <= colCount; j++ )
                 {
                     colList.add( rset.getMetaData().getColumnName( j ) );
                 }
                 jobList.add( colList );
                 while ( rset.next() )
                 {
                     colList = new ArrayList();
                     for ( int i = 1; i <= colCount; i++ )
                     {
                         dbColumn = new DBColumn( rset.getMetaData().getColumnName(i), rset.getObject(i),  rset.getMetaData().getColumnType(i) );
                         colList.add( dbColumn );
                     }
                     jobList.add( colList );
                 }
                 success = true;

             }
             catch ( Exception e )
             {
                 e.printStackTrace();
                 throw new ScalarException ( "Could not load Job Setup records", e );
             }
             finally
             {
                 if ( dbTransaction == null )
                 {
                     if ( conn != null )
                     {
                         try
                         {
                             if ( success )
                             {
                                 conn.commit();
                             }
                             else
                             {
                                 conn.rollback();
                             }
                         }catch ( SQLException e ){}
                     }
                }
                if ( rset != null )
                {
                    try { rset.close(); }catch ( SQLException ignore ) {}
                }
                if ( pstmt != null )
                {
                    try { pstmt.close(); } catch ( SQLException ignore ) {}
                }
                if ( conn != null )
                {
                    try { conn.close(); } catch ( SQLException ignore ) {}
                }
            }
            return jobList;
         }
    public static ReportConfiguration getReortConfiguration( DBTransaction dbTransaction, String reportName ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        ReportConfiguration reportConfiguration = null;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            pstmt   = conn.prepareStatement ( "SELECT REPORT_ID, REPORT_NAME, REPORT_CLASS, REPORT_METHOD, METHOD_PARAM1_TYPE, METHOD_PARAM2_TYPE, METHOD_PARAM3_TYPE, METHOD_PARAM4_TYPE, METHOD_PARAM5_TYPE, METHOD_PARAM1_VALUE, METHOD_PARAM2_VALUE, METHOD_PARAM3_VALUE, METHOD_PARAM4_VALUE, METHOD_PARAM5_VALUE, [SUBJECT], REPORT_FILE_NAME_WITH_EXTN, REPORT_METHOD_RETURN_TYPE, FILE_NAME_TIMESTAMP_FORMAT FROM REPORT_CONFIGURATION_TABLE WHERE REPORT_NAME = ?" );
            pstmt.setString ( 1, reportName );
            rset    = pstmt.executeQuery();

            if ( rset.next() )
            {
                reportConfiguration = new ReportConfiguration();
                reportConfiguration.setReportId( rset.getInt ( "REPORT_ID" ) );
                reportConfiguration.setReportName( rset.getString ( "REPORT_NAME" ) );
                reportConfiguration.setReportClassName( rset.getString ( "REPORT_CLASS" ) );
                reportConfiguration.setReportMethodName( rset.getString ( "REPORT_METHOD" ) );

                ArrayList methodParamTypeList = new ArrayList();
                methodParamTypeList.add ( rset.getString ( "METHOD_PARAM1_TYPE" ) );
                methodParamTypeList.add ( rset.getString ( "METHOD_PARAM2_TYPE" ) );
                methodParamTypeList.add ( rset.getString ( "METHOD_PARAM3_TYPE" ) );
                methodParamTypeList.add ( rset.getString ( "METHOD_PARAM4_TYPE" ) );
                methodParamTypeList.add ( rset.getString ( "METHOD_PARAM5_TYPE" ) );

                ArrayList methodParamValueList = new ArrayList();
                methodParamValueList.add ( rset.getString ( "METHOD_PARAM1_VALUE" ) );
                methodParamValueList.add ( rset.getString ( "METHOD_PARAM2_VALUE" ) );
                methodParamValueList.add ( rset.getString ( "METHOD_PARAM3_VALUE" ) );
                methodParamValueList.add ( rset.getString ( "METHOD_PARAM4_VALUE" ) );
                methodParamValueList.add ( rset.getString ( "METHOD_PARAM5_VALUE" ) );

                reportConfiguration.setSubject( rset.getString ( "SUBJECT" ) );
                reportConfiguration.setReportFileNameWithExtn( rset.getString ( "REPORT_FILE_NAME_WITH_EXTN" ) );
                reportConfiguration.setReportMethodReturnType( rset.getString ( "REPORT_METHOD_RETURN_TYPE" ) );
                reportConfiguration.setFilenameTimestampFormat( rset.getString ( "FILE_NAME_TIMESTAMP_FORMAT" ) );

                ArrayList paramTypesList = new ArrayList();
                ArrayList paramValueList = new ArrayList();
                Class paramTypeClass = null;
                Object paramValueObject = null;
                for ( int i=0; i < methodParamTypeList.size(); i++ )
                {
                    String paramType = (String)methodParamTypeList.get( i );
                    String paramValue = (String)methodParamValueList.get( i );
                    if ( !StringUtils.isNullOrEmpty( paramType ) )
                    {
                        if ( "PARAMETER_MAP".equalsIgnoreCase( paramValue ) ) //need to prepare Map from REPORT_PARAMETER_TABLE
                        {
                            paramType = "java.util.Map";
                            paramTypeClass = ClassUtils.forName( paramType );
                            paramValueObject = null;

                            rset.close(); rset = null;
                            pstmt.close(); pstmt = null;
                            pstmt = conn.prepareStatement( "SELECT MAP_PARAM_NAME, MAP_PARAM_VALUE FROM REPORT_PARAMETER_MAP_TABLE WHERE REPORT_ID = ?" );
                            pstmt.setInt( 1, reportConfiguration.getReportId() );
                            rset = pstmt.executeQuery();
                            Map parameterMap = new HashMap();
                            while ( rset.next() )
                            {
                                parameterMap.put ( rset.getString ( "MAP_PARAM_NAME" ), rset.getString ( "MAP_PARAM_VALUE" ) );
                            }

                            paramTypesList.add ( paramTypeClass );
                            paramValueList.add ( parameterMap.isEmpty() ? null : parameterMap );
                        }
                        else
                        {
                            paramTypeClass = ClassUtils.forName( paramType );
                            paramValueObject = ClassUtils.convertTo( paramValue, paramTypeClass );
                            paramTypesList.add ( paramTypeClass );
                            paramValueList.add ( paramValueObject );
                        }
                    }
                    else //IF NULL OR EMPTY MEANS PARAMS COMPLETED
                    {
                        break;
                    }
                }
                Class[] paramTypes = (Class[])paramTypesList.toArray(new Class[paramTypesList.size()]);
                Object[] paramValues = paramValueList.toArray(new Object[paramValueList.size()]);
                reportConfiguration.setParamTypes( paramTypes );
                reportConfiguration.setArgs( paramValues );
            }
        }
        catch ( Exception ex )
        {
            ex.printStackTrace();
            throw new ScalarException ( "Could not load Report Configuration for: " + reportName, ex );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return reportConfiguration;
    }
    public static String[] getJobEmailUserIds( DBTransaction dbTransaction, int jobId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        ArrayList emailUserIds  = new ArrayList();
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            pstmt   = conn.prepareStatement ( "SELECT USER_ID FROM JOB_EMAIL_USER_TABLE WHERE JOB_ID = ?" );
            pstmt.setInt ( 1, jobId );
            rset    = pstmt.executeQuery();
            while ( rset.next() )
            {
                emailUserIds.add( rset.getString ( "USER_ID" ) );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Job Email Users", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return (String[]) emailUserIds.toArray(new String[0]);
    }
    public static void saveJobEmailUserIds( DBTransaction dbTransaction, int jobId, String[] userIds ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        boolean success = false;
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            pstmt   = conn.prepareStatement ( "DELETE FROM JOB_EMAIL_USER_TABLE WHERE JOB_ID = ?" );
            pstmt.setInt ( 1, jobId );
            pstmt.executeUpdate();

            pstmt.close(); pstmt = null;
            pstmt = conn.prepareStatement ( "INSERT INTO JOB_EMAIL_USER_TABLE(JOB_ID, USER_ID) VALUES ( ?, ? )" );
            pstmt.setInt( 1, jobId );
            if ( userIds != null )
            {
                for ( int i=0; i < userIds.length; i++ )
                {
                    pstmt.setString( 2, userIds[i] );
                    pstmt.addBatch();
                }
            }
            pstmt.executeBatch();

            if ( dbTransaction == null )
            {
                conn.commit();
            }
            success = true;
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not save Job Email Users", e );
        }
        finally
        {
            if ( dbTransaction == null && !success && conn != null )
            {
                try { conn.rollback(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
    }
    public static String[] getJobAlertUserEmails( DBTransaction dbTransaction, int jobId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        ArrayList emails  = new ArrayList();
        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }
            pstmt   = conn.prepareStatement ( "SELECT EMAIL FROM JOB_EMAIL_USER_TABLE JEUT, USER_TABLE UT WHERE UT.USER_ID = JEUT.USER_ID AND UT.DISABLED = 1 AND EXPIRES_ON >= CAST( CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101) AS DATETIME) AND JOB_ID = ?" );
            pstmt.setInt ( 1, jobId );
            rset    = pstmt.executeQuery();
            while ( rset.next() )
            {
                emails.add( rset.getString ( "EMAIL" ) );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Job Email Users", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return (String[]) emails.toArray(new String[0]);
    }
    public static Workbook getJobsStatusReport ( DBTransaction dbTransaction, int pastHours ) throws ScalarException, IOException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        ArrayList reportDataList   = null;

        try
        {
            if ( dbTransaction != null )
            {
                conn    = dbTransaction.getConnection();
            }
            else
            {
                conn    = DataSource.getConnection();
            }

            pstmt   = conn.prepareStatement ( "SELECT J.JOB_NAME AS [JOB NAME], CONVERT(VARCHAR,JS.START_TIME,100) AS [START TIME], CONVERT(VARCHAR,JS.END_TIME,100) AS [END TIME], CAST(JS.TIME_TAKEN AS INT) [TIME_TAKEN (SEC)], JS.STATUS AS [STATUS] FROM JOB_STATUS JS, JOBS J WHERE J.JOB_ID = JS.JOB_ID AND JS.START_TIME >= DATEADD ( HH , -?, CURRENT_TIMESTAMP ) " );
            pstmt.setInt( 1, pastHours );
            rset    = pstmt.executeQuery();

            reportDataList = ExcelUtils.getAllResultSetRows( rset, true );
        }
        catch ( Exception e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Could not load cumm yield records", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); }catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        return  DataDumpReportsHelper.getWorkbook( reportDataList, "Past "+ pastHours +" Hrs Job(s) Status Report", null, null, null );
    }
}